﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using www.serviciipeweb.ro.Obj2Print;
using www.serviciipeweb.ro.CDC;
using Microsoft.SqlServer.Management.Common;
namespace CDCWnd
{
    public partial class frmConnection : Form
    {
        private enum bwWork
        {
            connectServer,
            ListTable,
            FindCDC
        }
        private Server m_Server;
        private Database m_Database;
        private Table m_Table;
        public frmConnection()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            btnConnect.Enabled = false;
            ConnectServer();
            
        }
        private void VoidData()
        {
            cmdDB.DataSource = null;
            lstTables.DataSource = null;
            propCDC.SelectedObject = null;
            dtgData.DataSource = null;
            btnTestTable.Enabled = false;
            btnRefresh.Enabled = false;
            btnPrint.Enabled = false;
            btnRestore.Enabled = false;
            m_Server = null;
            m_Database = null;
            m_Table = null;
            
        }
        private void ConnectServer()
        {
            bool bError = true;
            try
            {
                btnConnect.Enabled = false;
                VoidData();

                m_Server = new Server(txtServerName.Text);
                m_Server.InitializeConnection();
                eServerCDC cause;
                if (!m_Server.IsCDCEnabled(out cause))
                {
                    switch (cause)
                    {
                        case eServerCDC.OK:
                            //nothing
                            errProv.SetError(txtServerName, "");
                            break;
                        case eServerCDC.AgentNotRunning:
                            errProv.SetError(txtServerName, "Agent not running");
                            break;
                        default:
                            errProv.SetError(txtServerName, "");
                            MessageBox.Show(" Server problem : " + cause.ToString());
                            return;

                    }
                }
                bError = false;
            }
            catch (ConnectionFailureException ex)
            {
                MessageBox.Show(this, ex.Message , "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                btnConnect.Enabled = true;

            }
            if (!bError)
            {
                BindDB();
            }

        }

        
       

        private void frmConnection_Load(object sender, EventArgs e)
        {
            VoidData();
        }


        private void BindTables()
        {
            lstTables.DisplayMember = "Name";
            lstTables.ValueMember = "ID";
            lstTables.DataSource = m_Database.NonSystemOrSchema("cdc");

        }
        private void ConnectTables()
        {
            if (cmdDB.Items.Count == 0)
                return;
            if (cmdDB.SelectedValue == null)
                return;
            m_Database = m_Server.FromID(int.Parse(cmdDB.SelectedValue.ToString()));
            bool dbisCDC= m_Database.IsCDCEnabled();
            if (!dbisCDC)
                errProv.SetError(cmdDB, "DB not CDC enabled");
            else
                errProv.SetError(cmdDB, "");

            btnTestTable.Enabled = dbisCDC;
            btnEnableDatabase.Visible = !dbisCDC;
            BindTables();
        }
        private void cmdDB_SelectedIndexChanged(object sender, EventArgs e)
        {
           
            ConnectTables();

            
            
        }

        private void lstTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            btnRefresh.Enabled = true;
            if (lstTables.SelectedValue == null)
                return;
            if (m_Database == null)
                return;
            m_Table = m_Database.FromID(int.Parse(lstTables.SelectedValue.ToString()));
            BindTable();
            if (!m_Table.IsCDCEnabled())
            {
                if (!m_Table.HasPrimaryKeyIdentity())
                {
                    MessageBox.Show("Sorry ,we support only table with one column PK identity");
                    return;
                }
                if (MessageBox.Show(this, "Do you want to enable Change Data Capture on " + m_Table.Name + " ?", "CDC", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
                    return;

                m_Table.EnableCDCRecursive();

                if (m_Table.IsCDCEnabled())
                    MessageBox.Show("Modified CDC ,please modify(insert, update, delete) some data");

                BindTable();
            }
            
            
        }
        private void BindTable()
        {
            propCDC.SelectedObject = null;
            dtgData.DataSource = null;
            
            if (!m_Table.IsCDCEnabled())
                return;

            propCDC.SelectedObject = m_Table.TableCDC();
            if (m_Table.TableCDC().CDCEnabled)
            {
                BindChangesTable();
            }
        }
        private void BindChangesTable()
        {
            TableChangesCollection tcc = m_Table.TableCDC().AllChanges();
            bool HasChanges = (tcc != null);
            if (HasChanges)
            {
                
                dtgData.DataSource = tcc.InheritedData();
                
            }
            btnRestore.Enabled = HasChanges;
            btnPrint.Enabled = HasChanges;

        }
        private bool CanEnableCDC()
        {

            eServerCDC cause;
            if (!m_Server.IsCDCEnabled(out cause))
            {
                switch (cause)
                {
                    case eServerCDC.AgentNotRunning:
                        m_Server.EnableCDC();
                        break;
                    default:
                        MessageBox.Show("Can not update this server because :" + cause.ToString());
                        return false;
                }
            }
            if (!m_Database.IsCDCEnabled())
            {
                m_Database.ModifyCDC(true);
            }
            return true;
        }
        private void btnTestTable_Click(object sender, EventArgs e)
        {
            //TODO : number of minutes
            //http://technet.microsoft.com/en-us/library/bb510748.aspx
            //sys.sp_cdc_change_job @job_type = 'cleanup', @retention  = 52494800 
            //sys.sp_cdc_help_jobs
            if (!CanEnableCDC())
                return;
            string strNameEmpl ="Employee" + DateTime.Now.ToString("_yyyyMMdd_HHmmss");
            
            Table t =CreateTableEmployee(m_Database,strNameEmpl);
            t.EnableCDCRecursive();
            InsertValues(m_Database,strNameEmpl);
            t.TableCDC().Refresh();
            m_Database.Refresh();
            BindTables();
            lstTables.SelectedValue = t.ID;
            

        }
        private void InsertValues(Database d, string NameTableEmployee)
        {
            string DBName = d.Name;
            string s = "use " + DBName + "; insert into " + NameTableEmployee + "(FirstName,LastName,ManagerID) values";
            s +="('Andrei','Ignat',null),";//ID 1

            s += "('My','Manager',1),";//ID 2
            s += "('Other','Emlopyee',null)";//ID 3
            d.Parent.ConnectionContext.ExecuteNonQuery(s);
            System.Threading.Thread.Sleep(1000 * 20);
            d.Parent.ConnectionContext.ExecuteNonQuery("use " + DBName + ";update " + NameTableEmployee + " set LastName = 'Employee' where ID" +NameTableEmployee +" =3");
            System.Threading.Thread.Sleep(1000 * 45);
            d.Parent.ConnectionContext.ExecuteNonQuery("use " + DBName + ";delete from " + NameTableEmployee + " where ID" + NameTableEmployee + " =2");

            
            //Time to wait for read log
            System.Threading.Thread.Sleep(1000 * 35);
            
        }
        private Table CreateTableEmployee(Database d, string NameTableEmployee)
        {
            Table t = new Table(d, NameTableEmployee);
            Column ID = new Column(t, "ID" + NameTableEmployee, DataType.BigInt);
            ID.Nullable = false;
            ID.Identity = true;

            Column FirstName = new Column(t, "FirstName", DataType.VarChar(50));
            Column LastName = new Column(t, "LastName", DataType.VarChar(50));
            Column ManagerID = new Column(t, "ManagerID", DataType.BigInt);

            Index iPK = new Index(t,NameTableEmployee + "_PK");

            iPK.IsUnique = true;
            iPK.IsClustered = true;
            iPK.IndexedColumns.Add(new IndexedColumn(iPK, ID.Name));
            iPK.IndexKeyType = IndexKeyType.DriPrimaryKey;
            t.Indexes.Add(iPK);
            t.Columns.Add(ID);
            t.Columns.Add(FirstName);
            t.Columns.Add(LastName);
            t.Columns.Add(ManagerID);
            t.Create();
            return t;

        }

        
        
        private void BindDB()
        {
            cmdDB.DisplayMember = "Name";
            cmdDB.ValueMember = "ID";
            cmdDB.DataSource = m_Server.NonSystemDatabases();

        }

        private void btnRestore_Click(object sender, EventArgs e)
        {
            DataGridViewRow r = dtgData.CurrentRow;
            if (r == null)
            {
                MessageBox.Show("Please select in changes some data");
                return;
            }

            IModifiedTable i = r.DataBoundItem as IModifiedTable;
            DateTime dt = i.ModifiedDate;
            string NameTableRestore = m_Table.Name + "_From_" +dt.ToString("yyyyMMdd_HHmmss") +"_To_"+ DateTime.Now.ToString("yyyyMMdd_HHmmss");
            m_Table.TableCDC().AllChanges().RestoreTableToDate(dt, NameTableRestore);
            MessageBox.Show("Restored to " + NameTableRestore);
            
        }

        private void btnPrint_Click(object sender, EventArgs e)
        {
            List<object> tcc = dtgData.DataSource as List<object>;
            if(tcc == null || tcc.Count == 0)
            {
                MessageBox.Show("Please make some changes to the data ");
                return;
            }
            frmReport f = new frmReport();
            f.ReportDefinition = tcc[0].RDLTable_ForProperties();
            f.ReportData = tcc;
            f.Initialize();
            f.ShowDialog(this);
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {

            m_Table.TableCDC().Refresh();
            BindTable();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnAbout_Click(object sender, EventArgs e)
        {
            frmAbout f = new frmAbout();
            f.ShowDialog(this);
        }

        private void btnHelp_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("readme.htm");
        }

        private void btnEnableDatabase_Click(object sender, EventArgs e)
        {
            if (!CanEnableCDC())
                return;

            string DB = cmdDB.Text;
            VoidData();
            btnConnect.Enabled = false;
            ConnectServer();
            cmdDB.SelectedValue = DB;
        }
    }
}
